const JQTableIF = require('./index.js');
class JQTableImplement extends JQTableIF {
 	
	async get(id,keys) {
		// const db_op= require('../basic/dbconnection.js');
		let my_keys= keys?keys:this.keys;
		let sql = 'select ' +  my_keys.join(',') + ' from ' + this.table_name +
		' where ' + this.primary_key + '=?';
		let params=[];
		params.push(id);
		let result = await this.conn.readDB(sql,params);
		//console.log(JSON.stringify(result));
		if(result)return result[0];
		return null;
	    //console.log(`${this.name} makes a noise.`);
	}
	async remove(id) {
		if(!id)return null;
		let sql = 'delete from ' + this.table_name +
		' where ' + this.primary_key + '=?';
		let params=[];
		params.push(id);
		let result = await this.conn.updateDB(sql,params);		
		//upateDB ok {"fieldCount":0,"affectedRows":0,"insertId":0,"serverStatus":2,"warningCount":0,"message":"","protocol41":true
	    //,"changedRows":0}
		//upateDB::ok::{"fieldCount":0,"affectedRows":1,"insertId":0,"serverStatus":2,"warningCount":0,"message":"","protocol41":t
	   // rue,"changedRows":0}
	   if(result)return result.affectedRows;
		return -1;
	    //console.log(`${this.name} makes a noise.`);
	}
	
	async removes(ids) {
		if(!ids || ! ids.length)return null;
		let places= Array(ids.length).fill('?');
		let sql = 'delete from ' + this.table_name +
		' where ' + this.primary_key + ' in (' + places.join(',') + ')' ;
		console.log(sql +JSON.stringify(ids));
		let result = await this.conn.updateDB(sql,ids);		
		
		return result.affectedRows==ids.length;
	}
	
	async create(data,assign,on_dup) {
			
		if(!data)return null;
		
		console.log('create 1111' +(!data) + '  ' +data.length);
		let primary_value = null;
		if( Object.keys(data).includes(this.primary_key)){
			primary_value=data[this.primary_key];
			let temp = await this.get(primary_value,[this.primary_key]);
			if(temp)return await this.update(data,assign);
			delete data[this.primary_key];			
		}
		
		console.log('create 2222')
		
		/*
		var sql = ('insert into users_contacts(KEY_MATCH) values'+'(?,?,?,?,?,?,?,DATE_FORMAT(NOW(),\'%Y-%m-%d %H:%i:%S\')'+
		        ',DATE_FORMAT(NOW(),\'%Y-%m-%d %H:%i:%S\'))'+
		        '').replace('KEY_MATCH',keyvalue);//.replace('UPDATE_MATCH',updatekeyvalues);
		
		*/
		
		let keys=[];
		let holders=[];
		let values=[];
		this.keys.forEach((item)=>{
			if(data.hasOwnProperty(item)){
				keys.push(item);
				holders.push('?');
				values.push(data[item]);
			}
		})
		if(assign){
			this.keys.forEach((item)=>{
				if(!data.hasOwnProperty(item) && assign.hasOwnProperty(item)){
					keys.push(item);
					holders.push(assign[item]);
				}
			})
		}
		
		let updates=[];
		updates.push(`${this.primary_key}=${this.primary_key}`);
		
		if(on_dup){
			this.keys.forEach((item)=>{
				if(on_dup.hasOwnProperty(item)){
					updates.push(`${item}=?`);
					values.push(on_dup[item]);
				}
			})
		}
		console.log('create 3333')
		
		let sql = 'insert into ' + this.table_name +
		' ( ' + keys.join(',') + ' ) values (' + holders.join(',') + ') ' + 
		`on duplicate key update ` + updates.join(',') ;
		console.log(sql +JSON.stringify(values));
		let result = await this.conn.writeDB(sql,values);		
		console.log(JSON.stringify(result));
		//upateDB::ok::{"fieldCount":0,"affectedRows":2,"insertId":0,"serverStatus":2,"warningCount":0,"message":"","protocol41":t
	    //rue,"changedRows":0}	
		//{"fieldCount":0,"affectedRows":1,"insertId":768054,"serverStatus":2,"warningCount":0,"message":"","protocol41":true,"changedRows":0}	
	// writeDB::{"code":"ER_DUP_ENTRY","errno":1062,"sqlMessage":"Duplicate entry '94d1526006125232d-94' for key 'index5'","sqlState":"23000","index":0,"sql":"insert into orders ( o_number,o_money,d_id,u_id,service_state,serial_number,r
	// educed_price,handle_price,o_name,o_title,o_short_des,discount_price,o_time ) values ('94d1526006125232d',11,18,94,2,5,10.51,0.49,'新起点15','新起点15','新起点115',11,now())"}
	
	    if(result.insertId>0)return await this.get(result.insertId);
		return null;
	}
	
	async update_where(data,assign,params) {
	
		if(!data && !assign)return false;
		if(!params)return false;
	
		const JQSqlUtil1 = require('../utils/sql_util');
		params = JQSqlUtil1.parserQueryArray(params)
		console.log('update_where 2222')
	
		let values=[];
		if(!data)data ={};
		let updates=[];
		this.keys.forEach((item)=>{
			if(data.hasOwnProperty(item)){
				updates.push(`${item}=?`);
				values.push(data[item]);
			}
		})
		if(!assign)assign={};
		if(assign){
			this.keys.forEach((item)=>{
				if(!data.hasOwnProperty(item) && assign.hasOwnProperty(item)){
					updates.push(`${item}=${assign[item]}`);
				}
			})
		}
		
		let where ='';
		if(params){
			params.forEach((item)=>{
				if(item['constB'])where +=` ${item['constB']}`;
				if(item.hasOwnProperty('value')){
					where +='?';
					values.push(item['value']);
				}
				if(item['vals']){
					let arr = Array(item['vals'].length).fill('?');
					where +=`(${arr.join(',')})`;
					values = values.concat(item['vals']);
				}
				if(item['constE'])where +=`${item['constE']} `;
			})
		}
			
		let sql = `update  ${this.table_name} ` +
			`set  ${updates.join(',')} ` +
			`where ${where} `;
		let result = await this.conn.writeDB(sql,values);		
		console.log(JSON.stringify(result));
		if(!result ||!result.affectedRows)return false;
		return true;
		//upateDB::ok::{"fieldCount":0,"affectedRows":2,"insertId":0,"serverStatus":2,"warningCount":0,"message":"","protocol41":t
		//rue,"changedRows":0}	
		//{"fieldCount":0,"affectedRows":1,"insertId":768054,"serverStatus":2,"warningCount":0,"message":"","protocol41":true,"changedRows":0}	
	// writeDB::{"code":"ER_DUP_ENTRY","errno":1062,"sqlMessage":"Duplicate entry '94d1526006125232d-94' for key 'index5'","sqlState":"23000","index":0,"sql":"insert into orders ( o_number,o_money,d_id,u_id,service_state,serial_number,r
	// educed_price,handle_price,o_name,o_title,o_short_des,discount_price,o_time ) values ('94d1526006125232d',11,18,94,2,5,10.51,0.49,'新起点15','新起点15','新起点115',11,now())"}
	
	}
	async update(data,assign,on_dup) {
		if(!data)return null;
			
			console.log('update 1111' +(!data) + '  ' +data.length);
			let primary_value = null;
			if(data[this.primary_key]!=null){
				primary_value=data[this.primary_key];
				delete data[this.primary_key];			
			}
			
			console.log('update 2222')
			
			let pairs=[];
			let holders=[];
			let values=[];
			this.keys.forEach((item)=>{
				if(data.hasOwnProperty(item)){
					pairs.push(`${item}=?`);
					values.push(data[item]);
				}
			})
			if(assign){
				this.keys.forEach((item)=>{
					if(!data.hasOwnProperty(item) && assign.hasOwnProperty(item)){
						pairs.push(`${item}=${assign[item]}`);
					}
				})
			}
			values.push(primary_value);
			
			console.log('update 3333')
			if(pairs.length<=0)return null;
			
			
			// let updates=[];
			// updates.push(`${this.primary_key}=${this.primary_key}`);
			
			// if(on_dup){
			// 	this.keys.forEach((item)=>{
			// 		if(on_dup.hasOwnProperty(item)){
			// 			updates.push(`${item}=?`);
			// 			holders.push(on_dup[item]);
			// 		}
			// 	})
			// }
			
			// update  orders set  o_number=?,o_money=?,d_id=?,u_id=?,
			// service_state=?,serial_number=?,reduced_price=?,handle_price=?,o_name=?,
			// o_title=?,o_short_des=?,discount_price=? where o_id =?
			//  on duplicate key update o_id=o_id
			//  ["94d1526006125232d",11,18,94,2,5,10.51,0.49,"新起点16","新起点15",
			//  "新起点115",11,768054]
			// updateDB::before2025-04-16 09:19:59update  orders set  o_number=?,o_money=?,d_id=?,u_id=?,service_state=?,serial_number=?,reduced_price=?,handle_price=?,o_name=?,o_title=?,o_short_des=?,discount_price=? where o_id =? on duplicate
			//  key
			
			let sql = 'update  ' + this.table_name +
			' set  ' + pairs.join(',') + ' where ' + this.primary_key + ' =? ';
			 // +`on duplicate key update ${this.primary_key}=${this.primary_key}` ;
			console.log(sql +JSON.stringify(values));
			let result = await this.conn.updateDB(sql,values);		
			console.log(JSON.stringify(result));
			
			//upateDB::ok::{"fieldCount":0,"affectedRows":1,"insertId":0,"serverStatus":2,"warningCount":0,"message":"(Rows matched: 1  Changed: 1  Warnings: 0","protocol41":true,"changedRows":1}
		   
			return await this.get(primary_value);
	}
	
	 async updateSql(sql,params) {
		let result = await this.conn.updateDB(sql,params);		
		//upateDB::ok::{"fieldCount":0,"affectedRows":2,"insertId":0,"serverStatus":2,"warningCount":0,"message":"","protocol41":t
	    //rue,"changedRows":0}		
		return result;
	}
	 async querySql(sql,params) {
		let result = await this.conn.readDB(sql,params);		
		
		return result;
	}
	
	// 
	async query(alias_keys,params,first,size,group_by,order_by,is_count){
		console.log('query:: ' + 111);
		first=!first?0:first;
		size=!size?20:size;
	    alias_keys=!alias_keys?this.keys:alias_keys;
		const JQSqlUtil1 = require('../utils/sql_util');
		params = JQSqlUtil1.parserQueryArray(params)
		// if(!params)return null;
		
		let where ='';
		let values=[];
		if(params){
			params.forEach((item)=>{
				if(item['constB'])where +=` ${item['constB']}`;
				if(item.hasOwnProperty('value')){
					where +='?';
					values.push(item['value']);
				}
				if(item['vals']){
					let arr = Array(item['vals'].length).fill('?');
					where +=`(${arr.join(',')})`;
					values = values.concat(item['vals']);
				}
				if(item['constE'])where +=`${item['constE']} `;
			})
		}
		
		let sql = null;
		if(is_count)sql = `select count(*) total from(select  ${alias_keys.join(',')}  from ${this.table_name}`+
		  //       // ` where ${where} `+
				// ` where ${where} `+
				`${where? ' where  '+where:''}`+
				`${group_by? ' group by '+group_by:''}`+
				`  ${order_by? ' order by '+order_by:''}) a limit ?,?`;
		else sql = `select  ${alias_keys.join(',')}  from ${this.table_name}`+
		        // ` where ${where} `+
				` ${where? ' where  '+where:''}`+
				` ${group_by? ' group by '+group_by:''}`+
				`  ${order_by? ' order by '+order_by:''} limit ?,?`;		
		values.push(first);
		values.push(size);
		console.log('111111');		
		let result = await this.conn.readDB(sql,values);		
	    // console.log('query:: ' + 2222 +JSON.stringify(result) );
		return result;
		
	}
	
	async queryOne(alias_keys,params,group_by,order_by){
		
		let result = await this.query(alias_keys,params,0,1,group_by,order_by,0);
		if(result)return result[0];
		return null;
		
	}
	
	
	
	async count(alias_keys,params,order_by,group_by){
		
		let result = await this.query(alias_keys,params,0,1,order_by,group_by,true);
		if(!result)return 0;
		
		return result[0].total;
		
	}
	
	async query2(alias_keys,alias_a_keys, a_keys,a_params, b_table,alias_b_keys,b_keys,b_params,joint,on_a,on_b,params, first,size,group_by,order_by,is_count){
		console.log('query2:: ' + 111);
		if(!b_table)return null;
		first=!first?0:first;
		size=!size?20:size;
		let sql_a=null;
		let sql_b=null;
		let values=[];
		const JQSqlUtil1 = require('../utils/sql_util');
		
	    alias_a_keys=!alias_a_keys?['*']:alias_a_keys;
		let alias_a_keys_temp = alias_a_keys; //temp 用作alias
		alias_a_keys=[];
		alias_a_keys_temp.forEach((item, index) => {
		    alias_a_keys.push('a.'+alias_a_keys_temp[index]); // 直接修改原数组元素的值
		});
		console.log('query2:: ' + 22222);
		
		a_keys=!a_keys? this.keys:a_keys;
		a_params = JQSqlUtil1.parserQueryArray(a_params);
		let where_a='';
		if(a_params){
			a_params.forEach((item)=>{
				if(item['constB'])where_a +=` ${item['constB']}`;
				if(item.hasOwnProperty('value')){
					where_a +='?';
					values.push(item['value']);
				}
				if(item['vals']){
					let arr = Array(item['vals'].length).fill('?');
					where_a +=`(${arr.join(',')})`;
					values = values.concat(item['vals']);
				}
				if(item['constE'])where_a +=`${item['constE']} `;
			})
		}
		console.log('query2:: ' + 3333);
		
		sql_a= `(select ${a_keys.join(',')} from ${this.table_name} `
		       +`${where_a?'where ' + where_a:''}) a `
		
		alias_b_keys=!alias_b_keys?[]:alias_b_keys;
		// alias_b_keys.forEach((item, index) => {
		//     alias_b_keys[index] = 'b.' +item; // 直接修改原数组元素的值
		// });
		
		
		let alias_b_keys_temp = alias_b_keys; //temp 用作alias
		alias_b_keys=[];
		alias_b_keys_temp.forEach((item, index) => {
		    alias_a_keys.push('b.'+alias_b_keys_temp[index]); // 直接修改原数组元素的值
		});
		
		
		console.log(alias_b_keys+'   alias bkeys   ');
		b_keys=!b_keys? ['*']:b_keys;
		
		console.log(alias_b_keys+'   alias bkeys   ');
		b_params = JQSqlUtil1.parserQueryArray(b_params);
		let where_b='';
		console.log('query2:: ' + 44444);
		
		if(b_params){
			b_params.forEach((item)=>{
				if(item['constB'])where_b +=` ${item['constB']}`;
				if(item.hasOwnProperty('value')){
					where_b +='?';
					values.push(item['value']);
				}
				if(item['vals']){
					let arr = Array(item['vals'].length).fill('?');
					where_b +=`(${arr.join(',')})`;
					values = values.concat(item['vals']);
				}
				if(item['constE'])where_b +=`${item['constE']} `;
			})
		}
		console.log('query2:: ' + 5555);
		
		sql_b= `(select ${b_keys.join(',')} from ${b_table}`
		       +`${where_b?' where ' + where_b:''}) b `
		
		params = JQSqlUtil1.parserQueryArray(params);
		let where ='';
		if(params){
			params.forEach((item)=>{
				if(item['constB'])where +=` ${item['constB']}`;
				if(item.hasOwnProperty('value')){
					where +='?';
					values.push(item['value']);
				}
				if(item['vals']){
					let arr = Array(item['vals'].length).fill('?');
					where +=`(${arr.join(',')})`;
					values = values.concat(item['vals']);
				}
				if(item['constE'])where +=`${item['constE']} `;
			})
		}
		
		console.log('query2:: ' + 6666);
		
		let sql = null;
		alias_keys=alias_keys?alias_keys:['*'];
		let alias_ab_keys = alias_a_keys.concat(alias_b_keys);
		
		if(is_count)sql = `select count(*) total from ( select  ${alias_keys.join(',')} from `
				+`(select  ${alias_ab_keys.join(',')}  from ${sql_a} `
		        +` ${joint} ${sql_b} on a.${on_a}=b.${on_b} `
				+` ) ab ${group_by? ' group by '+group_by:''} ) t `
				+`  ${where? 'where ' + where:''}`; 
				//+` ${order_by? ' order by '+order_by:''}  limit ?,?`
		else sql =`select * from ( select  ${alias_keys.join(',')} from `
				+`(select  ${alias_ab_keys.join(',')}  from ${sql_a} `
		        +` ${joint} ${sql_b} on a.${on_a}=b.${on_b} `
				+` ) ab ${group_by? ' group by '+group_by:''} ) t `
				+`  ${where? 'where ' + where:''}` 
				+` ${order_by? ' order by '+order_by:''}  limit ?,?`
		
		values.push(first);
		values.push(size);
		console.log('query2:: ' + 7777);
				
		let result = await this.conn.readDB(sql,values);		
	
		return result;
		
	}
	
	
	async count2(alias_keys,alias_a_keys, a_keys,a_params,b_table,alias_b_keys, b_keys,b_params,joint,on_a,on_b,params, group_by,order_by){
		
		let result = await this.query2(alias_keys,alias_a_keys, a_keys,a_params,b_table,alias_b_keys, b_keys,b_params,
		                joint,on_a,on_b,params, 0,1,group_by,order_by,true);
		if(!result)return 0;
		
		return result[0].total;
		
	}
	
	
	
	
	
	
	
	
	
	
}
module.exports = JQTableImplement;